package com.vecchio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import com.vecchio.model.User;

public class UserDao {

	public User login(Connection connection,User user)throws SQLException {
		User LoginUser = null;
		String sql = "select * from users where username=? and password=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, user.getUsername());
		preparedStatement.setString(2, user.getPassword());
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			LoginUser = new User();
			LoginUser.setUserid(resultSet.getInt("userid"));
			LoginUser.setUsername(resultSet.getString("username"));
			LoginUser.setPassword(resultSet.getString("password"));
			LoginUser.setProvince(resultSet.getString("province"));
			LoginUser.setCity(resultSet.getString("city"));
			LoginUser.setPassword_question(resultSet.getString("password_question"));
			LoginUser.setPassword_answer(resultSet.getString("password_answer"));
		}
		return LoginUser;
	}
	public User getUserById(Connection connection,int userid)throws SQLException {
		User user = null;
		String sql = "select * from users where userid=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, userid);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			user = new User();
			user.setUserid(resultSet.getInt("userid"));
			user.setUsername(resultSet.getString("username"));
			user.setPassword(resultSet.getString("password"));
			user.setProvince(resultSet.getString("province"));
			user.setCity(resultSet.getString("city"));
			user.setPassword_question(resultSet.getString("password_question"));
			user.setPassword_answer(resultSet.getString("password_answer"));
		}
		return user;
	}
	public boolean addUser(Connection connection,User user) throws SQLException{
		String sql = "inster into users(username,password,email,province,city,password_question,password_answer) values(?,?,?,?,?,?,?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, user.getUsername());
		preparedStatement.setString(2, user.getPassword());
		preparedStatement.setString(3, user.getEmail());
		preparedStatement.setString(4, user.getProvince());
		preparedStatement.setString(5, user.getCity());
		preparedStatement.setString(6, user.getPassword_question());
		preparedStatement.setString(7, user.getPassword_answer());
		int row = preparedStatement.executeUpdate();
		if (row > 0) {
			return true;
		}
		return false;
	}
	
	public User getUserByUserName(Connection connection,String username)throws Exception {
		User user = null;
		String sql = "select * from users where username=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, username);
		ResultSet resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			user = new User();
			user.setUserid(resultSet.getInt("userid"));
			user.setUsername(resultSet.getString("username"));
			user.setPassword(resultSet.getString("password"));
			user.setProvince(resultSet.getString("province"));
			user.setCity(resultSet.getString("city"));
			user.setPassword_question(resultSet.getString("password_question"));
			user.setPassword_answer(resultSet.getString("password_answer"));
		}
		return user;
		
	}
	public boolean updateUserInfo(Connection connection,User user)throws Exception {
		int row = -1;
		String sqlString = "update users set username=?,password=?,email=?,province=?,city=?,password_question=?,password_answer=?";
		PreparedStatement preparedStatement = connection.prepareStatement(sqlString);
		preparedStatement.setString(1, user.getUsername());
		preparedStatement.setString(2, user.getPassword());
		preparedStatement.setString(3, user.getEmail());
		preparedStatement.setString(4, user.getProvince());
		preparedStatement.setString(5, user.getCity());
		preparedStatement.setString(6, user.getPassword_question());
		preparedStatement.setString(7, user.getPassword_answer());
		row = preparedStatement.executeUpdate();
		if (row>=0) {
			return true;
		}
		return false;
	}
}
